# !/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2024/9/12 09:57
# @Author  : 王凯
# @File    : gy_clean.py
# @Project : scrapy_spider
import datetime

import pandas as pd

from apps.data_stats.data_stats.clean import BaseClean
import warnings
warnings.filterwarnings("ignore")


class GYClean(BaseClean):

    def deal_df_one(self, df_one, name_mapping):
        if 'name' not in df_one.columns:
            df_one["name"] = df_one["tag_name"].map(lambda x: x.split("_")[0])
        item = df_one[["name", "num"]].set_index("name").T.rename(columns=name_mapping).to_dict("index")["num"]
        item.update({"time": df_one["date_string"].tolist()[0], "category": df_one["category"].tolist()[0]})
        return item

    def save_data(self, datas, table):
        batch_size = 1000
        for i in range(0, len(datas), batch_size):
            self.to_db.add_batch_smart(table, list(datas[i:i + batch_size]), update_columns=list(datas[0].keys()))

    def run_gy_jjzb(self):

        def replace_category(src):
            if src in ["本月末", "累计值"]:
                return "当期值"
            elif src in ["上年同期累计值", "上年同期"]:
                return "上年同期值"
            elif src in ["增减", "累计增长"]:
                return "同比增速(%)"
            return src

        df = self.get_data_from_db(
            cate_1="工业", cate_2="工业企业主要经济指标", condition=" and area = '全国' and data_type = '月度数据'"
        )
        df["category"] = df["tag_name"].map(lambda x: replace_category(x.split("_")[1]))
        df = df[df["date_string"].str.startswith(tuple(str(i) for i in range(1999, datetime.datetime.now().year + 1)))]
        name_mapping = {
            "企业单位数": "number_of_company",
            "亏损企业": "loss_company",
            "流动资产": "current_assets",
            "流动资产合计": "current_assets",
            "应收账款": "accounts_receivable",
            "存货": "inventory",
            "产成品存货": "finished_goods",
            "资产总计": "assets",
            "负债合计": "liabilities",
            "主营业务收入": "main_business_income",
            "主营业务成本": "main_business_cost",
            "主营业务税金及附加": "main_business_taxes_and_surcharges",
            "销售费用": "selling_expenses",
            "管理费用": "administrative_expenses",
            "财务费用": "financial_expenses",
            "利息支出": "interest_expense",
            "投资收益": "income_from_investment",
            "营业利润": "operating_profit",
            "利润总额": "total_profit",
            "亏损企业亏损总额": "total_amount_of_loss_making_enterprises",
            "应交增值税": "vat_payable",
            "应收票据及应收账款": "accounts_receivable_and_notes_receivable",
            "营业收入": "operating_revenue",
            "营业成本": "operating_costs",
            "利息费用": "interest_fee",
            "所有者权益": "total_owners_equity",
            "所有者权益合计": "total_owners_equity",
            "平均用工人数": "average_number_of_workers",
        }
        res = (
            df.groupby(["date_string", "category"])
            .apply(lambda df_one: self.deal_df_one(df_one, name_mapping))
            .tolist()
        )
        self.save_data(res, "net_economic_indicators_of_industrial_enterprises")

    def run_gy_zc(self):

        def replace_category(src):
            if src in ["本月末", "累计值"]:
                return "当期值"
            elif src in ["上年同期累计值", "上年同期"]:
                return "上年同期值"
            elif src in ["增减", "累计增长"]:
                return "同比增速(%)"
            else:
                raise Exception(f"error {src}")

        df_1 = self.get_data_from_db(cate_1="工业", cate_2="按登记注册类型分工业企业主要经济指标（-2023）", condition=" and area = '全国' and data_type = '月度数据'")
        df_2 = self.get_data_from_db(cate_1="工业", cate_2="按登记注册类型分工业企业主要经济指标（2024-）", condition=" and area = '全国' and data_type = '月度数据'")
        df = pd.concat([df_1, df_2])
        df["category"] = df["tag_name"].map(lambda x: replace_category(x.split("_")[1]))
        df["registration_type"] = df["cate_3"].str.extract(r"(.*)(?:[\(（].*[\)）])*企业主要经济指标")

        df["tag_name"] = df["tag_name"].str.extract(f"(?:{'|'.join(df['registration_type'].unique().tolist())})*(?:企业)*(.*)")
        df = df[df["date_string"].str.startswith(tuple(str(i) for i in range(2001, datetime.datetime.now().year + 1)))]
        name_mapping = {
            "单位数": "number_of_company",
            "企业单位数": "number_of_company",
            "亏损企业": "loss_company",
            "亏损企业数": "loss_company",
            "流动资产": "current_assets",
            "流动资产合计": "current_assets",
            "应收账款": "accounts_receivable",
            "存货": "inventory",
            "产成品存货": "finished_goods",
            "资产总计": "assets",
            "负债合计": "liabilities",
            "主营业务收入": "main_business_income",
            "主营业务成本": "main_business_cost",
            "主营业务税金及附加": "main_business_taxes_and_surcharges",
            "销售费用": "selling_expenses",
            "管理费用": "administrative_expenses",
            "财务费用": "financial_expenses",
            "利息支出": "interest_expense",
            "投资收益": "income_from_investment",
            "营业利润": "operating_profit",
            "利润总额": "total_profit",
            "亏损企业亏损总额": "total_amount_of_loss_making_enterprises",
            "应交增值税": "vat_payable",
            "应收票据及应收账款": "accounts_receivable_and_notes_receivable",
            "营业收入": "operating_revenue",
            "营业成本": "operating_costs",
            "利息费用": "interest_fee",
            "所有者权益": "total_owners_equity",
            "所有者权益合计": "total_owners_equity",
            "平均用工人数": "average_number_of_workers",
        }
        res = (
            df.groupby(["date_string", "category", 'registration_type'])
            .apply(lambda df_one: self.deal_df_one(df_one, name_mapping))
            .tolist()
        )
        self.save_data(res, "net_reg_type_economic_indicators_of_industrial_enterprises")

    def run_gy_dzqy_1(self):
        def replace_category(src):
            if src in ["本月末", "累计值"]:
                return "当期值"
            elif src in ["上年同期累计值", "上年同期"]:
                return "上年同期值"
            elif src in ["增减", "累计增长"]:
                return "同比增速(%)"
            return src

        df = self.get_data_from_db(cate_1="工业", cate_2="大中型工业企业主要经济指标", condition=" and area = '全国' and data_type = '月度数据'")
        df["category"] = df["tag_name"].map(lambda x: replace_category(x.split("_")[1]))
        df["type"] = '大中型工业企业'
        df = df[df["date_string"].str.startswith(tuple(str(i) for i in range(2001, datetime.datetime.now().year + 1)))]
        name_mapping = {
            "大中型工业企业企业单位数": "number_of_company",
            "大中型工业企业单位数": "number_of_company",
            "大中型工业企业亏损企业": "loss_company",
            "大中型工业企业亏损企业数": "loss_company",
            "大中型工业企业流动资产": "current_assets",
            "大中型工业企业流动资产合计": "current_assets",
            "大中型工业企业应收账款": "accounts_receivable",
            "大中型工业企业存货": "inventory",
            "大中型工业企业产成品存货": "finished_goods",
            "大中型工业企业资产总计": "assets",
            "大中型工业企业负债合计": "liabilities",
            "大中型工业企业主营业务收入": "main_business_income",
            "大中型工业企业主营业务成本": "main_business_cost",
            "大中型工业企业主营业务税金及附加": "main_business_taxes_and_surcharges",
            "大中型工业企业销售费用": "selling_expenses",
            "大中型工业企业管理费用": "administrative_expenses",
            "大中型工业企业财务费用": "financial_expenses",
            "大中型工业企业利息支出": "interest_expense",
            "大中型工业企业投资收益": "income_from_investment",
            "大中型工业企业营业利润": "operating_profit",
            "大中型工业企业利润总额": "total_profit",
            "大中型工业企业亏损企业亏损总额": "total_amount_of_loss_making_enterprises",
            "大中型工业企业应交增值税": "vat_payable",
            "大中型工业企业应收票据及应收账款": "accounts_receivable_and_notes_receivable",
            "大中型工业企业营业收入": "operating_revenue",
            "大中型工业企业营业成本": "operating_costs",
            "大中型工业企业利息费用": "interest_fee",
            "大中型工业企业所有者权益": "total_owners_equity",
            "大中型工业企业所有者权益合计": "total_owners_equity",
            "大中型工业企业平均用工人数": "average_number_of_workers",
        }
        res = (
            df.groupby(["date_string", "category"])
            .apply(lambda df_one: self.deal_df_one(df_one, name_mapping))
            .tolist()
        )
        self.save_data(res, "net_large_and_medium_sized_economic_indicators_of_industrial_ent")

    def run_gy_dzqy_2(self):
        def replace_category(src):
            if src in ["本月末", "累计值"]:
                return "当期值"
            elif src in ["上年同期累计值", "上年同期"]:
                return "上年同期值"
            elif src in ["增减", "累计增长"]:
                return "同比增速(%)"
            else:
                raise Exception(src)

        df = self.get_data_from_db(cate_1="工业", cate_2="大中型国有控股工业企业主要经济指标", condition=" and area = '全国' and data_type = '月度数据'")
        df["category"] = df["tag_name"].map(lambda x: replace_category(x.split("_")[1]))
        df["type"] = '大中型国有控股工业企业主要经济指标'
        df = df[df["date_string"].str.startswith(tuple(str(i) for i in range(2001, datetime.datetime.now().year + 1)))]
        name_mapping = {
            "大中型国有控股工业企业企业单位数": "number_of_company",
            "大中型国有控股工业企业单位数": "number_of_company",
            "大中型国有控股工业企业亏损企业": "loss_company",
            "大中型国有控股工业企业亏损企业数": "loss_company",
            "大中型国有控股工业企业流动资产": "current_assets",
            "大中型国有控股工业企业流动资产合计": "current_assets",
            "大中型国有控股工业企业应收账款": "accounts_receivable",
            "大中型国有控股工业企业存货": "inventory",
            "大中型国有控股工业企业产成品存货": "finished_goods",
            "大中型国有控股工业企业资产总计": "assets",
            "大中型国有控股工业企业负债合计": "liabilities",
            "大中型国有控股工业企业主营业务收入": "main_business_income",
            "大中型国有控股工业企业主营业务成本": "main_business_cost",
            "大中型国有控股工业企业主营业务税金及附加": "main_business_taxes_and_surcharges",
            "大中型国有控股工业企业销售费用": "selling_expenses",
            "大中型国有控股工业企业管理费用": "administrative_expenses",
            "大中型国有控股工业企业财务费用": "financial_expenses",
            "大中型国有控股工业企业利息支出": "interest_expense",
            "大中型国有控股工业企业投资收益": "income_from_investment",
            "大中型国有控股工业企业营业利润": "operating_profit",
            "大中型国有控股工业企业利润总额": "total_profit",
            "大中型国有控股工业企业亏损企业亏损总额": "total_amount_of_loss_making_enterprises",
            "大中型国有控股工业企业应交增值税": "vat_payable",
            "大中型国有控股工业企业应收票据及应收账款": "accounts_receivable_and_notes_receivable",
            "大中型国有控股工业企业营业收入": "operating_revenue",
            "大中型国有控股工业企业营业成本": "operating_costs",
            "大中型国有控股工业企业利息费用": "interest_fee",
            "大中型国有控股工业企业所有者权益": "total_owners_equity",
            "大中型国有控股工业企业所有者权益合计": "total_owners_equity",
            "大中型国有控股工业企业平均用工人数": "average_number_of_workers",
        }
        res = (
            df.groupby(["date_string", "category"])
            .apply(lambda df_one: self.deal_df_one(df_one, name_mapping))
            .tolist()
        )
        self.save_data(res, "net_large_and_medium_sized_economic_indicators_of_industrial_ent")

    def run(self):
        self.run_gy_jjzb()
        self.run_gy_zc()
        self.run_gy_dzqy_1()
        self.run_gy_dzqy_2()


if __name__ == "__main__":
    GYClean().run()
